import sqlite3
import time
from datetime import datetime

# 注册 datetime 适配器和转换器
def adapt_datetime(dt):
    return time.mktime(dt.timetuple())

def convert_datetime(stamp):
    return datetime.fromtimestamp(float(stamp))

sqlite3.register_adapter(datetime, adapt_datetime)
sqlite3.register_converter("timestamp", convert_datetime)

def get_db_connection():
    return sqlite3.connect('example.db', detect_types=sqlite3.PARSE_DECLTYPES)

def init_db():
    conn = get_db_connection()
    c = conn.cursor()
    # 创建用户表和单词表
    c.execute('''CREATE TABLE IF NOT EXISTS users
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                 name TEXT NOT NULL,
                 email TEXT NOT NULL)''')
    # 创建单词表
    c.execute('''CREATE TABLE IF NOT EXISTS words
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                 chinese TEXT NOT NULL,
                 hiragana TEXT NOT NULL,
                 category TEXT NOT NULL,
                 meaning TEXT NOT NULL,
                 add_time DATETIME,
                 last_test_time DATETIME)''')
    # 错题本 表
    c.execute('''CREATE TABLE IF NOT EXISTS wrong_book
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                 word_id INTEGER NOT NULL,
                 error_time DATETIME NOT NULL,
                 error_count INTEGER NOT NULL)''')

    conn.commit()
    conn.close()